package com.yuews.codegenerator.parser;

import com.yuews.codegenerator.model.ColumnInfo;
import com.yuews.codegenerator.model.TableInfo;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL解析器 - 解析CREATE TABLE语句
 */
public class SqlParser implements TableInfoParser {

    @Override
    public String getParserType() {
        return "sql";
    }
    
    /**
     * 解析SQL建表语句，支持多表解析
     * @param sql SQL建表语句
     * @return 表信息列表
     */
    @Override
    public List<TableInfo> parse(String sql) {
        List<TableInfo> tables = new ArrayList<>();
        
        // 清理SQL语句
        sql = cleanSql(sql);
        
        // 分割多个CREATE TABLE语句
        String[] createTableStatements = sql.split("(?=CREATE\\s+TABLE)", -1);
        
        for (String createStatement : createTableStatements) {
            createStatement = createStatement.trim();
            if (!createStatement.isEmpty() && createStatement.toUpperCase().startsWith("CREATE TABLE")) {
                // 解析单个表
                TableInfo tableInfo = parseSingleTable(createStatement);
                if (tableInfo != null && tableInfo.getTableName() != null) {
                    tables.add(tableInfo);
                }
            }
        }
        
        return tables;
    }
    
    /**
     * 解析单个表的建表语句
     * @param sql 单个表的建表语句
     * @return 表信息对象
     */
    private TableInfo parseSingleTable(String sql) {
        // 创建表信息对象
        TableInfo tableInfo = new TableInfo();
        
        // 移除SQL中的注释和多余空格
        sql = cleanSql(sql);
        
        // 提取表名
        extractTableName(sql, tableInfo);
        
        // 提取表注释
        extractTableComment(sql, tableInfo);
        
        // 提取字段信息
        extractColumns(sql, tableInfo);
        
        // 提取主键信息
        extractPrimaryKey(sql, tableInfo);
        
        // 提取索引信息
        extractIndexes(sql, tableInfo);
        
        return tableInfo;
    }

    /**
     * 清理SQL语句，移除注释和多余空格
     */
    private String cleanSql(String sql) {
        // 移除行注释
        Pattern lineCommentPattern = Pattern.compile("--.*$", Pattern.MULTILINE);
        sql = lineCommentPattern.matcher(sql).replaceAll("");
        // 移除块注释
        Pattern blockCommentPattern = Pattern.compile("/\\*.*?\\*/", Pattern.DOTALL);
        sql = blockCommentPattern.matcher(sql).replaceAll("");
        // 移除多余空格和换行
        Pattern whitespacePattern = Pattern.compile("\\s+");
        sql = whitespacePattern.matcher(sql).replaceAll(" ");
        return sql.trim();
    }

    /**
     * 提取表名
     */
    private void extractTableName(String sql, TableInfo tableInfo) {
        Pattern pattern = Pattern.compile("CREATE\\s+TABLE\\s+([^\\s(]+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            String tableName = matcher.group(1);
            // 移除可能的引号或反引号
            tableName = tableName.replaceAll("['`]", "");
            tableName = tableName.replace("\"", "");
            tableInfo.setTableName(tableName);
        }
    }

    /**
     * 提取表注释
     */
    private void extractTableComment(String sql, TableInfo tableInfo) {
        // Oracle风格注释
        Pattern oraclePattern = Pattern.compile("COMMENT\\s+ON\\s+TABLE\\s+[^\\s]+\\s+IS\\s+'([^']+)',", Pattern.CASE_INSENSITIVE);
        Matcher oracleMatcher = oraclePattern.matcher(sql);
        if (oracleMatcher.find()) {
            tableInfo.setTableComment(oracleMatcher.group(1));
            return;
        }

        // MySQL风格注释
        Pattern mysqlPattern = Pattern.compile("COMMENT[ ]*=[ ]*['`]?([^'`\n;]+)", Pattern.CASE_INSENSITIVE);
        Matcher mysqlMatcher = mysqlPattern.matcher(sql);
        if (mysqlMatcher.find()) {
            tableInfo.setTableComment(mysqlMatcher.group(1));
        }
    }

    /**
     * 提取字段信息
     */
    private void extractColumns(String sql, TableInfo tableInfo) {
        // 提取括号内的字段定义部分
        Pattern tableBodyPattern = Pattern.compile("\\(([^\\)]+)\\)", Pattern.DOTALL);
        Matcher tableBodyMatcher = tableBodyPattern.matcher(sql);
        
        if (tableBodyMatcher.find()) {
            String tableBody = tableBodyMatcher.group(1);
            // 按逗号分割字段（注意处理括号内的逗号）
            String[] columnDefs = splitColumns(tableBody);
            
            for (String columnDef : columnDefs) {
                columnDef = columnDef.trim();
                // 跳过主键、索引等约束定义
                if (columnDef.startsWith("PRIMARY KEY") || columnDef.startsWith("UNIQUE") || 
                    columnDef.startsWith("INDEX") || columnDef.startsWith("KEY") || 
                    columnDef.isEmpty() || columnDef.startsWith("FOREIGN KEY")) {
                    continue;
                }
                
                ColumnInfo column = parseColumn(columnDef, sql);
                if (column != null) {
                    tableInfo.addColumn(column);
                }
            }
        }
    }

    /**
     * 智能分割字段定义（处理括号内的逗号）
     */
    private String[] splitColumns(String tableBody) {
        StringBuilder sb = new StringBuilder();
        int bracketCount = 0;
        
        for (char c : tableBody.toCharArray()) {
            if (c == '(') {
                bracketCount++;
            } else if (c == ')') {
                bracketCount--;
            } else if (c == ',' && bracketCount == 0) {
                sb.append('|'); // 用|作为分隔符
                continue;
            }
            sb.append(c);
        }
        
        return sb.toString().split("\\|");
    }

    /**
     * 解析单个字段定义
     */
    private ColumnInfo parseColumn(String columnDef, String fullSql) {
        ColumnInfo column = new ColumnInfo();
        
        // 提取字段名和类型
        Pattern columnPattern = Pattern.compile("(\\w+)\\s+([^\\s,]+)", Pattern.CASE_INSENSITIVE);
        Matcher columnMatcher = columnPattern.matcher(columnDef);
        
        if (columnMatcher.find()) {
            column.setColumnName(columnMatcher.group(1));
            column.setDataType(columnMatcher.group(2));
            
            // 提取字段长度
            Pattern lengthPattern = Pattern.compile("\\((\\d+)\\)");
            Matcher lengthMatcher = lengthPattern.matcher(columnDef);
            if (lengthMatcher.find()) {
                try {
                    column.setLength(Integer.parseInt(lengthMatcher.group(1)));
                } catch (NumberFormatException e) {
                    column.setLength(0);
                }
            }
            
            // 检查PRIMARY KEY约束
            if (columnDef.toUpperCase().contains("PRIMARY KEY")) {
                column.setPrimaryKey(true);
            }
            
            // 检查NOT NULL约束
            if (columnDef.toUpperCase().contains("NOT NULL")) {
                column.setNotNull(true);
            }
            
            // 检查DEFAULT值
            Pattern defaultPattern = Pattern.compile("DEFAULT\\s+('[^']+'|\"[^\"]+\"|\\w+)", Pattern.CASE_INSENSITIVE);
            Matcher defaultMatcher = defaultPattern.matcher(columnDef);
            if (defaultMatcher.find()) {
                column.setDefaultValue(defaultMatcher.group(1));
            }
            
            // 检查自增
            if (columnDef.toUpperCase().contains("AUTO_INCREMENT") || 
                columnDef.toUpperCase().contains("IDENTITY") ||
                columnDef.toUpperCase().contains("GENERATED BY DEFAULT AS IDENTITY")) {
                column.setAutoIncrement(true);
            }
            
            // 提取字段注释
            extractColumnComment(column.getColumnName(), fullSql, column);
            
            return column;
        }
        
        return null;
    }

    /**
     * 提取字段注释
     */
    private void extractColumnComment(String columnName, String fullSql, ColumnInfo column) {
        // Oracle风格注释
        Pattern oraclePattern = Pattern.compile("COMMENT\\s+ON\\s+COLUMN\\s+[^.]+\\." + columnName + "\\s+IS\\s+'([^']+)',?", Pattern.CASE_INSENSITIVE);
        Matcher oracleMatcher = oraclePattern.matcher(fullSql);
        if (oracleMatcher.find()) {
            column.setColumnComment(oracleMatcher.group(1));
            return;
        }
        
        // MySQL风格注释
        Pattern mysqlPattern = Pattern.compile(columnName + "\\s+[^,]*\\s+COMMENT\\s*['`]\\s*([^'`]+)\\s*['`]", Pattern.CASE_INSENSITIVE);
        Matcher mysqlMatcher = mysqlPattern.matcher(fullSql);
        if (mysqlMatcher.find()) {
            column.setColumnComment(mysqlMatcher.group(1));
        }
    }

    /**
     * 提取主键信息
     */
    private void extractPrimaryKey(String sql, TableInfo tableInfo) {
        // 1. 先检查是否有字段被标记为主键（字段级PRIMARY KEY）
        for (ColumnInfo column : tableInfo.getColumns()) {
            if (column.isPrimaryKey()) {
                tableInfo.setPrimaryKey(column.getColumnName());
                return;
            }
        }
        
        // 2. 表级PRIMARY KEY定义
        Pattern pkPattern = Pattern.compile("PRIMARY\\s+KEY\\s*\\(([^\\)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher pkMatcher = pkPattern.matcher(sql);
        if (pkMatcher.find()) {
            String primaryKey = pkMatcher.group(1).trim();
            // 移除可能的引号
            primaryKey = primaryKey.replaceAll("['`]", ""); // 移除单引号和反引号
            primaryKey = primaryKey.replace("\"", ""); // 移除双引号
            tableInfo.setPrimaryKey(primaryKey);
            
            // 更新对应字段的主键标识
            for (ColumnInfo column : tableInfo.getColumns()) {
                if (column.getColumnName().equalsIgnoreCase(primaryKey)) {
                    column.setPrimaryKey(true);
                    break;
                }
            }
        }
    }

    /**
     * 提取索引信息
     */
    private void extractIndexes(String sql, TableInfo tableInfo) {
        Pattern indexPattern = Pattern.compile("(INDEX|KEY)\\s+([^\\s(]+)\\s*\\(([^\\)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher indexMatcher = indexPattern.matcher(sql);
        
        while (indexMatcher.find()) {
            String indexName = indexMatcher.group(2);
            String indexColumns = indexMatcher.group(3);
            tableInfo.addIndex(indexName + "(" + indexColumns + ")");
        }
    }


}